﻿Public Class PROVIDERCLASSPOLICIES
    ''' <summary>
    ''' select provider ทั้งหมดจาก table provider
    ''' </summary>
    Dim sql As String = "SELECT CONCAT(`name`,' ',lname) AS `provider` FROM myfriendsdb.hospemp "
    ''' <summary>
    ''' select รายชื่อพนักงานจาก table hospemp โดยให้ในรูปแบบกลุ่ม
    ''' </summary>
    ''' <remarks>ปัจจุบันไม่ใช้ เพราะ table usringrp ไม่มีการใช้แล้ว</remarks>
    Dim sql1 As String = "SELECT    usrpolicy.upid , usrpolicy.empid , usrpolicy.prgid , max(usrpolicy.padd) AS PADD  , max(usrpolicy.pdel) AS PDEL , max( usrpolicy.pupd ) AS PUPD , max(usrpolicy.pprt ) AS PPRT , max(psee) AS PSEE, prgcode ,prgname ,usreq , name ,lname  FROM myfriendsdb.hospemp JOIN usringrp ON hospemp.empid = usringrp.empid JOIN  grppolicy ON usringrp.grpusrid = grppolicy.grpusrid "
    ''' <summary>
    ''' ใช้ map กับ sql เพื่อใช้ในการเลือกข้อมูลด้วย WHERE PROVIDER
    ''' </summary>
    ''' <remarks>input: IDUSER as string</remarks>
    ''' WHERE provider='" & txtUserReq.Text & "' AND status='1';"
    ''' <returns>dataTable</returns>

    Public Function GETPROVIDER(IDUSER As String) As DataTable
        Dim db As CONDB = CONDB.NewConnection
        Dim dt As DataTable = New DataTable
        Dim sql2 As String = sql + "  WHERE provider ='" & IDUSER & "';"
        dt = db.GetTable(sql2)
        db.Dispose()
        Return dt
    End Function

    ''' <summary>
    ''' select รายชื่อหมอ
    ''' </summary>
    ''' <remarks>input: CLINICID as string</remarks>
    ''' <returns>dataTable</returns>
    Public Function GETDoctorZone(CLICNICID As String) As DataTable

        Dim dt As DataTable = New DataTable
        Dim db As CONDB = CONDB.NewConnection
        Dim sql As String = "SELECT p.empid as 'provider',p.registerno,CONCAT(p.`name`,' ',p.lname) AS DOCTORNAME,COUNT(w.vn) AS Wait ,COUNT(s.VN) AS Complete ,p.F_LOGIN  FROM  myfriendsdb.hospemp AS p   LEFT  JOIN     "
        sql += "(SELECT * FROM myfriendsdb.frnclinic WHERE CLINIC = " + Convert.ToString(CLICNICID).Trim + ") AS c  ON c.DOCTOR = p.PROVIDER "
        sql += "LEFT JOIN (SELECT * FROM myfriendsdb.frnclinic WHERE CLINIC = " + Convert.ToString(CLICNICID).Trim + " AND CSTATUS IN(1,2,3)) AS w ON c.VN = w.VN "
        sql += "LEFT JOIN (SELECT * FROM myfriendsdb.frnclinic WHERE CLINIC = " + Convert.ToString(CLICNICID).Trim + " AND CSTATUS IN(4,5,6)) AS s ON c.VN = s.VN "
        sql += " "
        sql += "WHERE    p.CLINIC =  " + Convert.ToString(CLICNICID).Trim + " GROUP BY c.DOCTOR"

        dt = db.GetTable(sql)
        Return dt
    End Function
    ''' <summary>
    ''' ใช้ map กับ sql1 เพื่อใช้ในการเลือกข้อมูลด้วย WHERE USEREQ
    ''' </summary>
    ''' <remarks>input: IDUSER as string</remarks>
    ''' <returns>dataTable</returns>
    Public Function GETHOSEM(IDUSER As String) As DataTable
        Dim dt As DataTable = New DataTable
        Dim db As CONDB = CONDB.NewConnection
        Dim sql2 As String = sql1 + " WHERE  usreq = '" & IDUSER & "'"
        dt = db.GetTable(sql2)
        db.Dispose()
        Return dt
    End Function

    
End Class
